﻿Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.IO
Imports System.Drawing.Imaging
Module Materiel
    'Tarik
    'variables
    Public frm As Form
    Public idParrai As Integer = 0
    Public idFournGnlr As Integer = 0
    Public idClt As Integer = 0
    Public idSrv As Integer = 0
    Public somParrain As sommeParain
    Public lstPaiement As List(Of paiement)
    Public cheq As cheque
    Public effetObj As effet
    Public objVirement As virement
    Public nomin As nominatif
    Public rest As Double
    Public isPerson As Boolean 'Pour séparer le nom du prénom s'il s'agit d'une personne
    Public isSelectedChanged As Boolean = True 'Savoir s'il faut déclancher l'événement selected changed pour le combo Client
    Public sr As StreamReader
    Public sw As StreamWriter
    Public serveur As String = ""
    Public nomClient As String 'Pour stocker le nom du clients dans les deux cas (Loc et Evenement) voir l'interface Frm_Paiement_Client
    Public repasExist As Boolean
    Public objEquipement As equipement
    Public objTypEquip As typeEquipement
    Public dteLoc As System.DateTime
    Public numLocation As Integer
    'End

    'Tarik
    'Fonctions et structure
    Public Function regDate(ByVal dte As String) As Boolean
        Dim myRegex As System.Text.RegularExpressions.Regex
        myRegex = New Regex("^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$")
        Dim l As Boolean = myRegex.IsMatch(dte)
        If Not l Then
            myRegex = New Regex("^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d$")
            l = myRegex.IsMatch(dte)
        End If
        Return l
    End Function
    Public Structure sommeParain
        Public idParain As Integer
        Public somme As Double
    End Structure
    Public Structure cheque
        Public idPai As Integer
        Public numChe As String
        Public nominatif As Boolean
        Public dteRetra As Date
        Public dateChequ As Date
        Public banqueDestinataire As String
        Public banqueDestinateur As String
        Public typePersonne As Char
    End Structure
    Public Structure virement
        Public idPai As Integer
        Public numCompte As String
        Public dateVirement As Date
        Public banqueDestinataire As String
        Public banqueDestinateur As String
        Public nom As String
        Public typePersonne As Char
    End Structure

    Public Structure nominatif
        Public idNomin As Integer
        Public idPai As Integer
        Public nom As String
        Public prenom As String
    End Structure

    Public Structure effet
        Public idPai As Integer
        Public numEffet As String
        Public dteEffet As Date
        Public dteRetrait As Date
        Public dateEcheance As Date
        Public nominatif As Boolean
        Public banqueDestinataire As String
        Public banqueDestinateur As String
        Public typePersonne As Char
    End Structure

    Public Sub cnClose()
        Try
            If Con.State = ConnectionState.Open Then
                Con.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Sub open()
        Try
            If Con.State = ConnectionState.Closed Then
                Con.Open()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Public Function convertToBit(ByVal b As Boolean) As String
        If b Then
            Return "1"
        Else
            Return "0"
        End If
    End Function

    Public Sub maVider(ByVal fr As Form, Optional ByVal tnom As ArrayList = Nothing)
        Dim classment As Integer = 0
        Dim flag As Boolean
        For Each txt As Control In fr.Controls


            flag = True
            If Not tnom Is Nothing Then
                For Each nom As String In tnom
                    If txt.Name = nom Then
                        flag = False
                        Exit For
                    End If
                Next
            End If


            If flag Then
                If TypeOf txt Is TextBoxBase Then
                    CType(txt, TextBoxBase).Clear()
                ElseIf TypeOf txt Is ComboBox Then
                    If CType(txt, ComboBox).Items.Count > 0 Then
                        CType(txt, ComboBox).SelectedIndex = 0
                    End If
                ElseIf TypeOf txt Is ListView Then
                    If CType(txt, ListView).SelectedIndices.Count > 0 Then
                        For i As Integer = 0 To CType(txt, ListView).SelectedIndices.Count - 1
                            CType(txt, ListView).Items(CType(txt, ListView).SelectedIndices(i)).Selected = False
                        Next
                    End If
                ElseIf TypeOf txt Is RadioButton Then
                    classment += 1
                    If classment = 1 Then
                        CType(txt, RadioButton).Checked = True
                    End If
                ElseIf TypeOf txt Is MaskedTextBox Then
                    CType(txt, MaskedTextBox).Clear()
                ElseIf TypeOf txt Is DateTimePicker Then
                    CType(txt, DateTimePicker).Value = Now
                End If
            End If
        Next
    End Sub
    'Public selectedChangedCmb As Boolean = False
    Public Sub remplirCmb(ByRef comb As ComboBox, ByVal req As String)
        Dim str As String = ""
        comb.Items.Clear()
        Dim cmd As New SqlClient.SqlCommand(req, Con)
        open()
        Dr = cmd.ExecuteReader
        While Dr.Read
            comb.Items.Add(New DictionaryEntry With {.Key = Dr.GetInt32(0), .Value = Dr.GetString(1)})
        End While
        Dr.Close()
        cnClose()

        comb.DisplayMember = "value"
        comb.ValueMember = "key"
        If comb.Items.Count > 0 Then
            'selectedChangedCmb = True
            comb.SelectedIndex = -1
        End If
    End Sub
    Public lstParrai As List(Of paraineur)
    Public Sub remplirLstParrai()
        lstParrai = New List(Of paraineur)
        Cmd = New SqlCommand("select * from Paraineur", Con)
        open()
        Dr = Cmd.ExecuteReader
        If Dr.HasRows Then
            While Dr.Read
                lstParrai.Add(New paraineur With {.idParai = Dr.GetInt32(0), .nom = Dr.GetString(1), .prenom = Dr.GetString(2), .tel = Dr.GetString(3), .adrss = Dr.GetString(4)})
            End While
        End If
        Dr.Close()
        cnClose()
    End Sub

    Public lstClients As List(Of Clients)
    Public Sub remplirLstClt()
        lstClients = New List(Of Clients)
        Cmd = New SqlCommand("SELECT [Id_Client],[Nom],[Prenom],Adresse FROM [Clients]", Con)
        open()
        Dr = Cmd.ExecuteReader
        If Dr.HasRows Then
            While Dr.Read
                lstClients.Add(New Clients With {.idClt = Dr.GetInt32(0), .nom = Dr.GetString(1), .prenom = Dr.GetString(2), .adrss = Dr.GetString(3)})
            End While
        End If
        Dr.Close()
        cnClose()
    End Sub

    Public lstFournisseurGnlr As List(Of Fournisseurs)
    Public Sub remplirLstFourGnrl()
        lstFournisseurGnlr = New List(Of Fournisseurs)
        Cmd = New SqlCommand("SELECT [Id_Fournisseur] ,[Nom] " &
                            ",[Prenom] " &
                            ",[Adresse] " &
                            ",[Email] " &
                            ",[Nom_entreprise] " &
                            ",[Type_Fournisseur] " &
                            ",[type] " &
                            "FROM [Salle_Mariage].[dbo].[Fournisseus]", Con)
        open()
        Dr = Cmd.ExecuteReader
        If Dr.HasRows Then
            While Dr.Read
                lstFournisseurGnlr.Add(New Fournisseurs With {.idFr = Dr.GetInt32(0), .type = Dr.GetString(7), .nom = Dr.GetString(1), .prenom = Dr.GetString(2), _
                                                        .adrss = Dr.GetString(3), .email = Dr.GetString(4), .nomEntrp = Dr.GetString(5), _
                                                              .typeFour = Dr.GetString(6)})
            End While
        End If
        Dr.Close()
        cnClose()
    End Sub


    Public Sub remplirLstPayementFourn(ByVal idSrv As Integer)
        lstPaiement = New List(Of paiement)
        Cmd = New SqlCommand("select * from paiements " &
                             "where idPai in (select idPai from paiementFour where idServ = " & idSrv & ")", Con)
        open()
        Dr = Cmd.ExecuteReader
        If Dr.HasRows Then
            While Dr.Read
                lstPaiement.Add(New paiement With {.idPai = Dr.GetInt32(0), .montant = Dr.GetDouble(1), .type = Dr.GetString(2)})
            End While
        End If
        Dr.Close()
        cnClose()
    End Sub

    Public Function nullPourDate(ByVal dte As Date) As String
        If Format(dte, "MM-dd-yyyy") = "01-01-0001" Then
            Return "NULL"
        Else
            Return "'" & Format(dte, "MM-dd-yyyy") & "'"
        End If
    End Function

    Public Function convertNullToDate(ByVal dte As Object) As Date
        If dte Is DBNull.Value Then
            Return New Date(1, 1, 1)
        Else
            Return Date.Parse(dte)
        End If
    End Function

    Public Function convertNullToString(ByVal dte As Object, ByVal ret As String)
        If dte Is DBNull.Value Then
            Return ret
        Else
            Return dte
        End If
    End Function
    'End

    Public lstAdjoints As List(Of adjoint)
    Public Sub remplirLstAdjoint(ByVal req As String)
        lstAdjoints = New List(Of adjoint)
        Cmd = New SqlCommand(req, Con)
        Dr = Cmd.ExecuteReader
        If Dr.HasRows Then
            While Dr.Read
                lstAdjoints.Add(New adjoint With {.idAdjoint = Dr.GetInt32(0), .nom = Dr.GetString(1), _
                                .prenom = Dr.GetString(2), .professino = Dr.GetString(3), .tel = Dr.GetString(4), .adresse = Dr.GetString(5)})
            End While
        End If
        Dr.Close()
    End Sub

    Public Sub remplirCmbAdjoint(ByRef cmb As ComboBox)
        cmb.Items.Clear()
        cmb.Text = ""
        Dim htClient As New Hashtable()
        For Each adj In lstAdjoints
            htClient.Add(adj.idAdjoint, adj.nom & " " & adj.prenom)
        Next
        For Each ImageType As DictionaryEntry In htClient
            cmb.Items.Add(ImageType)
        Next
        cmb.DisplayMember = "value"
        cmb.ValueMember = "key"
        If cmb.Items.Count > 0 Then
            cmb.SelectedIndex = 0
        End If
    End Sub

    Public eventAdjoinLst As List(Of adjointEvents)

    Public Sub remplirLstAdjoint(ByVal idEvent As Integer)
        Cmd = New SqlCommand("Select * From adjointEvenement where id_dem = " & idEvent, Con)
        Dr = Cmd.ExecuteReader()
        While Dr.Read()
            eventAdjoinLst.Add(New adjointEvents With {.idAdjoint = Dr.GetInt32(0), .id_dem = idEvent, .prix = Dr.GetDouble(2)})
        End While
    End Sub

    Public Function convertPicToByte(ByVal img As Image) As Byte()
        Dim ms As MemoryStream = New MemoryStream()
        img.Save(ms, ImageFormat.Jpeg)
        Dim bytBLOBData(ms.Length - 1) As Byte
        ms.Position = 0
        ms.Read(bytBLOBData, 0, ms.Length)
        Return bytBLOBData
    End Function

    Public Function convertByteToImage(ByVal bytBLOBData() As Byte) As Image
        Dim stmBLOBData As New MemoryStream(bytBLOBData)
        Return Image.FromStream(stmBLOBData)
    End Function
    'End fonctions
    Public lstEquipement As List(Of equipement)

    Public Sub remplirEquip()
        Cmd = New SqlCommand("SELECT * FROM [Salle_Mariage].[dbo].[trEquipement]", Con)
        Dr = Cmd.ExecuteReader
            lstEquipement = New List(Of equipement)
            While Dr.Read
            lstEquipement.Add(New equipement With {.idEquip = Dr.GetInt32(0), .categorie = Dr.GetString(1)})
            End While
            Dr.Close()
    End Sub
    Public sub ajouterEquipement(ByVal equip As equipement) 
        Cmd = New SqlCommand("INSERT INTO [Salle_Mariage].[dbo].[trEquipement] " &
           "(categorie) VALUES (@categorie)", Con)
        Cmd.Parameters.Add("@categorie", SqlDbType.VarChar).Value = equip.categorie
        Cmd.ExecuteNonQuery()
        Cmd.Parameters.Clear()
        Cmd.CommandText = "select @@identity"
        equip.idEquip = Cmd.ExecuteScalar
        lstEquipement.Add(equip)
    End Sub
    Public Sub supprimerEquip(ByVal idEqui As Integer)
        Cmd = New SqlCommand("delete from [trEquipement] where id = " & idEqui, Con)
        Cmd.ExecuteNonQuery()
        lstEquipement.Remove((From equi In lstEquipement Where equi.idEquip = idEqui)(0))
    End Sub
    Public Sub modifierEquipement(ByVal equip As equipement)
        Cmd = New SqlCommand("UPDATE [Salle_Mariage].[dbo].[trEquipement] " &
            "SET [categorie] = @categorie WHERE id = " & equip.idEquip, Con)
        Cmd.Parameters.Add("@categorie", SqlDbType.VarChar).Value = equip.categorie
        Cmd.ExecuteNonQuery()
    End Sub
    Public Function convertDbnullToInt(ByVal int As Object) As Integer
        If int Is DBNull.Value Then
            Return 0
        Else
            Return int
        End If
    End Function
End Module
